package com.fengxing.ams.web.task;

import java.io.File;
import java.lang.reflect.InvocationTargetException;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import com.fengxing.ams.pub.AMSException;
import com.fengxing.ams.pub.manager.intf.IConfigManager;
import com.fengxing.ams.pub.util.MD5Util;
import com.fengxing.ams.pub.util.StringUtil;
import com.fengxing.ams.pub.vo.UploadRuleVO;
import com.fengxing.ams.web.facade.intf.IStudentFacade;
import com.fengxing.ams.web.facade.mouble.company.CompanyImportVO;
import com.fengxing.ams.web.facade.mouble.company.ICompanyImportDAO;
import com.fengxing.ams.web.facade.mouble.student.IStudentSCMDAO;
import com.fengxing.ams.web.facade.mouble.student.StudentSCMVO;
import com.fengxing.ams.web.facade.mouble.upload.IUploadDetailDAO;
import com.fengxing.ams.web.facade.mouble.upload.UploadDetailVO;
import com.fengxing.ams.web.facade.mouble.upload.UploadVO;
import com.fengxing.ams.web.facade.mouble.user.IRoleDAO;
import com.fengxing.ams.web.facade.mouble.user.IUserDAO;
import com.fengxing.ams.web.facade.mouble.user.UserVO;
import com.fengxing.ams.web.task.translator.Translator;

@Component
@Transactional
public class ParseExcelTask {

	private static final Log logger = LogFactory.getLog(ParseExcelTask.class);
	@Autowired
	IStudentFacade studentFacade;
	@Autowired
	IConfigManager configManager;
	
	public void parseAction() {
		long lastTime = System.currentTimeMillis();
		logger.info("parseAction start:" + lastTime);
		
		//加载所有未处理的文件
		List<UploadVO> uploads = this.studentFacade.queryUploadsForParse();
		for (UploadVO upload : uploads) {
			if (upload.getType().equals("student")) {
				studentImport(upload);
			} else if (upload.getType().equals("company")) {
				companyImport(upload);
			}
		}
		
		logger.info("parseAction end:" + (System.currentTimeMillis() - lastTime));
	}
	
	private void companyImport(UploadVO upload) {
		//获取excel文件
				File excelFile = new File(upload.getFilePath());
				if (!excelFile.exists() || !excelFile.canRead()) {
					//文件丢失或不可读，更新状态以及备注
					this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部失败", "文件丢失或不可读");
					return;
				}
				//解析文件
				//读取表格  
				Sheet sheet = null;
				int totalRows = 0;
				try {
					Workbook workbook = Workbook.getWorkbook(excelFile);
					sheet = workbook.getSheet(0);
					totalRows = sheet.getRows();
				} catch (Throwable e) {
					this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部失败", "文件类型不是Excel97-2003或格式损坏！");
					return;
				}
				
				if (totalRows <= 0) {
					this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部失败", "Excel没有任何数据！");
					return;
				}
				boolean isRowError = false;
				boolean isRowSuccess = false;
				for (int i = 1; i < totalRows; i++) {
					Cell[] cells = sheet.getRow(i);
					//每一行都应该生成一个结果和一个学生对象
					UploadDetailVO uploadDetail = new UploadDetailVO();
					uploadDetail.setUploadId(upload.getId());
					//cell长度必须大于22
					if (cells.length < 15) {
						this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部失败", "列数量不够，期望>=15，实际"+ cells.length);
						break;
					}
					CompanyImportVO company = new CompanyImportVO();
					boolean isCellError = false;
					for (int j = 0; j < cells.length; j++) {
						String content = cells[j].getContents();
						if (j == 0 && StringUtils.isBlank(content))
							continue;
						
						//先获取规则
						UploadRuleVO rule = this.configManager.getTranlatorRule(j, upload.getType());
						if (rule == null)
							continue;
						//先进行校验
						if (rule.getPattern() != null && !content.matches(rule.getPattern())) {
							uploadDetail.setRemark(rule.getRemark() + ", 实际值：" + content);
							uploadDetail.setRowNum(i+1);
							uploadDetail.setCellNum(j+1);
							uploadDetail.setStatus("失败");
							isCellError = true;
							break;
						}
						//查看是否有Translator
						if (StringUtil.isNotEmpty(rule.getTranslator())) {
							
							Translator trans = null;
							try {
								trans = (Translator) Class.forName(rule.getTranslator()).newInstance();
							} catch (InstantiationException e1) {
							} catch (IllegalAccessException e1) {
							} catch (ClassNotFoundException e1) {
							}
							try {
								content = trans.translate(content);
							} catch (AMSException e) {
								uploadDetail.setRemark(e.getMessage());
								uploadDetail.setRowNum(i+1);
								uploadDetail.setCellNum(j+1);
								uploadDetail.setStatus("失败");
								isCellError = true;
								break;
							}
						}
						//然后设置对应属性
						try {
							BeanUtils.setProperty(company, rule.getPropertyName(), content);
						} catch (IllegalAccessException e) {
						} catch (InvocationTargetException e) {
						}
						if (j == 0) {
							if (this.configManager.isExist(content)) {
								uploadDetail.setRemark("重复的序号：" + content);
								uploadDetail.setRowNum(i+1);
								uploadDetail.setCellNum(j+1);
								uploadDetail.setStatus("失败");
								isCellError = true;
								break;
							}
						}
					}
					if (isCellError) {
						//保存upload detail，学生无需保存
						isRowError = true;
						saveUploadDetail(uploadDetail);
					} else {
						//学生和upload detail一块保存
						uploadDetail.setRowNum(i+1);
						uploadDetail.setStatus("成功");
						saveUploadDetail(uploadDetail);
						this.configManager.addStudentId(company.getId());
						saveCompany(company);
						isRowSuccess = true;
					}
				}
				if( isRowError && isRowSuccess) {
					this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "部分成功", "");
				} else if (!isRowError && isRowSuccess) {
					this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部成功", "");
				} else if (!isRowSuccess && isRowError) {
					this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部失败", "");
				}
	}

	@Autowired
	ICompanyImportDAO companyImportDAO;
	private void saveCompany(CompanyImportVO company) {
		this.companyImportDAO.save(company);
	}

	private void studentImport(UploadVO upload) {
		//获取excel文件
		File excelFile = new File(upload.getFilePath());
		if (!excelFile.exists() || !excelFile.canRead()) {
			//文件丢失或不可读，更新状态以及备注
			this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部失败", "文件丢失或不可读");
			return;
		}
		//解析文件
		//读取表格  
		Sheet sheet = null;
		int totalRows = 0;
		try {
			Workbook workbook = Workbook.getWorkbook(excelFile);
			sheet = workbook.getSheet(0);
			totalRows = sheet.getRows();
		} catch (Throwable e) {
			this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部失败", "文件类型不是Excel97-2003或格式损坏！");
			return;
		}
		
		if (totalRows <= 0) {
			this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部失败", "Excel没有任何数据！");
			return;
		}
		boolean isRowError = false;
		boolean isRowSuccess = false;
		for (int i = 1; i < totalRows; i++) {
			Cell[] cells = sheet.getRow(i);
			//每一行都应该生成一个结果和一个学生对象
			UploadDetailVO uploadDetail = new UploadDetailVO();
			uploadDetail.setUploadId(upload.getId());
			//cell长度必须大于22
			if (cells.length < 22) {
				this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部失败", "列数量不够，期望>=22，实际"+ cells.length);
				break;
			}
			StudentSCMVO student = new StudentSCMVO();
			boolean isCellError = false;
			for (int j = 0; j < cells.length; j++) {
				//先获取规则
				UploadRuleVO rule = this.configManager.getTranlatorRule(j, upload.getType());
				if (rule == null)
					continue;
				//先进行校验
				String content = cells[j].getContents();
				if (rule.getPattern() != null && !content.matches(rule.getPattern())) {
					uploadDetail.setRemark(rule.getRemark() + ", 实际值：" + content);
					uploadDetail.setRowNum(i+1);
					uploadDetail.setCellNum(j+1);
					uploadDetail.setStatus("失败");
					isCellError = true;
					break;
				}
				//如果是学号，检查是否有重复的
				
				//查看是否有Translator
				if (StringUtil.isNotEmpty(rule.getTranslator())) {
					
					Translator trans = null;
					try {
						trans = (Translator) Class.forName(rule.getTranslator()).newInstance();
					} catch (InstantiationException e1) {
					} catch (IllegalAccessException e1) {
					} catch (ClassNotFoundException e1) {
					}
					try {
						content = trans.translate(content);
					} catch (AMSException e) {
						uploadDetail.setRemark(e.getMessage());
						uploadDetail.setRowNum(i+1);
						uploadDetail.setCellNum(j+1);
						uploadDetail.setStatus("失败");
						isCellError = true;
						break;
					}
				}
				//然后设置对应属性
				try {
					BeanUtils.setProperty(student, rule.getPropertyName(), content);
				} catch (IllegalAccessException e) {
				} catch (InvocationTargetException e) {
				}
				if (j == 0) {
					if (this.configManager.isExist(content)) {
						uploadDetail.setRemark("重复的学号：" + content);
						uploadDetail.setRowNum(i+1);
						uploadDetail.setCellNum(j+1);
						uploadDetail.setStatus("失败");
						isCellError = true;
						break;
					}
						
				}
			}
			if (isCellError) {
				//保存upload detail，学生无需保存
				isRowError = true;
				saveUploadDetail(uploadDetail);
			} else {
				//学生和upload detail一块保存
				uploadDetail.setRowNum(i+1);
				uploadDetail.setStatus("成功");
				saveUploadDetail(uploadDetail);
				saveStduent(student);
				this.configManager.addStudentId(student.getId());
				isRowSuccess = true;
			}
		}
		if( isRowError && isRowSuccess) {
			this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "部分成功", "");
		} else if (!isRowError && isRowSuccess) {
			this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部成功", "");
		} else if (!isRowSuccess && isRowError) {
			this.studentFacade.updateUploadStatusAndRemark(upload.getId(), "全部失败", "");
		}
	}
	
	@Autowired
	IUploadDetailDAO uploadDetailDAO;
	@Autowired
	IUserDAO userDAO;
	@Autowired
	IRoleDAO roleDAO;
	@Autowired
	IStudentSCMDAO studentSCMDAO;
	
	@Transactional(readOnly = false)
	private void saveUploadDetail(UploadDetailVO uploadDetailVO) {
		uploadDetailDAO.save(uploadDetailVO);
	}
	
	@Transactional(readOnly = false)
	private void saveStduent(StudentSCMVO studentVO) {
		//生成用户信息s
		UserVO userVO = new UserVO();
		String studentId = studentVO.getId();
		userVO.setName(studentVO.getName());
		userVO.setEmail(studentId);
		userVO.setPassword(MD5Util.md5(StringUtils.substring(studentId, studentId.length() - 6, studentId.length())+studentId));
		userVO.setCreateDate(new Date());
		userVO.setLastLoginDate(new Date());
		userVO.getRoleList().add(roleDAO.findOne(4L));
		
		this.userDAO.save(userVO);
		//保存学生信息
		studentSCMDAO.save(studentVO);
	}
	
}
